﻿using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using UnityEngine;

namespace Smart.Editor
{
    public class Excel
    {
        FileStream mFileStream;
        IWorkbook mWorkBook;
        ISheet mSheet;
        string mError;

        public bool IsError
        {
            get
            {
                return !string.IsNullOrEmpty(mError);
            }
        }

        public string Error
        {
            get
            {
                return mError;
            }
            private set
            {
                mError = value;
                Smart.Model.Logger.LogError(mError);
            }
        }

        void ReportMsg(string fmt, params object[] argv)
        {
            Smart.Model.Logger.Log($"<color=#00ff00>{string.Format(fmt, argv)}</color>");
        }

        public Excel(string path, FileAccess accessMode = FileAccess.Read)
        {
            if (!path.EndsWith(".xls"))
            {
                mError = "File Type Must Be .xls";
                return;
            }

            mFileStream = new FileStream(path, FileMode.Open, accessMode, FileShare.ReadWrite);
            if (null == mFileStream)
            {
                mError = "File Not Exist";
                return;
            }
            ReportMsg($"Load [{System.IO.Path.GetFileName(path)}] Succeed !");

            mWorkBook = new HSSFWorkbook(mFileStream);
            mSheet = mWorkBook.GetSheetAt(0);
            if (null == mSheet)
            {
                mError = "Get Sheet Failed At Index 0";
                return;
            }

            var pureName = System.IO.Path.GetFileNameWithoutExtension(path);
            if (!string.Equals(mSheet.SheetName, pureName))
            {
                mError = $"SheetName Must The Same With FileName [SheetName:{mSheet.SheetName}] [FileName:{pureName}]";
                return;
            }
        }

        public void Read(System.Action<string,string[,]> actionRow)
        {
            string[,] datas = new string[0,0];
            var sheetName = string.Empty;
            if (!IsError && null != mSheet)
            {
                sheetName = mSheet.SheetName;
                List<IRow> validRows = new List<IRow>(mSheet.LastRowNum + 1);
                int coloums = mSheet.LastRowNum + 1;
                for(int i = 0; i < coloums;++i)
                {
                    IRow row = mSheet.GetRow(i);
                    if(null == row || row.LastCellNum <= 0)
                    {
                        continue;
                    }

                    validRows.Add(row);
                }

                if(validRows.Count > 0 && validRows[0].LastCellNum >= 0)
                {
                    datas = new string[validRows.Count, validRows[0].LastCellNum];
                    for(int j = 0,maxj = validRows.Count;j < maxj;++j)
                    {
                        var row = validRows[j];
                        for (int i = 0, max = row.LastCellNum; i < max; ++i)
                        {
                            string value = string.Empty;
                            if (row.GetCell(i) is ICell cell)
                            {
                                switch (cell.CellType)
                                {
                                    case CellType.Numeric:
                                        value = ((int)cell.NumericCellValue).ToString();
                                        break;
                                    case CellType.String:
                                        value = cell.StringCellValue.Trim();
                                        break;
                                    case CellType.Blank:
                                        value = string.Empty;
                                        break;
                                    case CellType.Formula:
                                        if (cell.CachedFormulaResultType == CellType.Numeric)
                                        {
                                            value = ((int)cell.NumericCellValue).ToString();
                                        }
                                        else if (cell.CachedFormulaResultType == CellType.String)
                                        {
                                            value = cell.StringCellValue.Trim();
                                        }
                                        break;
                                    default:
                                        value = string.Empty;
                                        break;
                                }
                            }
                            datas[j,i] = value;
                        }
                    }
                }
            }
            actionRow(sheetName, datas);
        }

        public void Close()
        {
            mWorkBook?.Close();
            mWorkBook = null;
            mFileStream?.Dispose();
            mFileStream = null;
        }
    }
}